import functools
from functools import wraps
import redis
import jwt
import xlsxwriter as xlsxwriter
from flask import Blueprint, request, make_response, jsonify
import os
from flask import Blueprint
from xlrd import open_workbook

from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
from db import Dd
import datetime
import mimetypes
from jwttoken import Myjwt

views = Blueprint('views', __name__)


# 装饰器
def timer(func):
    @functools.wraps(func)
    def decor(*args, **kwargs):
        token = request.args.get('token')
        username = request.args.get('username')
        id = request.args.get('id')
        print(token)
        if token:
            jwts = Myjwt()
            data = jwts.decode_jwt(token)
            print(data)
            print(data['username'])
            if data['username'] == username and data['id'] == id:
                return {'code': '403', 'msg': 'token权限验证失败,请重新登录'}
        return func(*args, **kwargs)

    return decor


db1 = Dd()


# 导入excel
@views.route('/upload', methods=['POST'])
def uploads():
    print(request.files)
    fileilename = request.files['file']
    print(request.files['file'])
    fileilename.save(os.path.join('./static/' + fileilename.filename))
    name = 'static/' + fileilename.filename
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    # 行
    for i in range(2, nrows):
        row_date = sh.row_values(i)
        for x in range(0, ncols):
            print(row_date[x])
    return 'ok'


# 导出exls
@views.route('/download', methods=["GET", "POST"])
def download():
    # 实例化二进制数据
    out = BytesIO()
    # 创建一个Excel实例
    workbook = xlsxwriter.Workbook(out)
    # 添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table = workbook.add_worksheet()
    # table.write(行, 列, 字段名)
    # 行、列从 0 开始计数
    # 字段命名根据每个人使用情况而定，插入数据部分也是
    table.write(0, 0, "资产编号")
    table.write(0, 1, "资产名称")

    workbook.close()
    filename = quote("资产列表_{}.xlsx".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file = make_response(out.getvalue())
    out.close()
    mime_type = mimetypes.guess_type('%s.xlsx' % filename)[0]
    file.headers['Content-Type'] = mime_type
    file.headers["Cache-Control"] = "no-cache"
    file.headers['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(filename)
    return file


# 获取无限极
@views.route('/acporgn', methods=["GET"])
@timer
def acporgn():
    sql = "select * from `organ`"
    db = Dd()
    orgn = db.get_all(sql)
    if not orgn:
        return orgn
    #  先把列表重组成字典
    dict1 = {}
    list1 = []
    for i in orgn:
        i['label'] = i['name']
        dict1[i['id']] = i
    for j in orgn:
        level = j['level']
        j['label'] = j['name']
        #  pid等于0，就是顶级，
        if level == 0:
            # 直接加入列表
            list1.append(j)
        else:
            #  查看父类是否有子类
            if 'children' not in dict1[level]:
                dict1[level]['children'] = []
            dict1[level]['children'].append(j)
    return {'code': '200', 'list1': list1}


# #  无限极分类数据重构展示

# 添加数据
@views.route('/reliable_orgn', methods=['POST'])
def reliable_orgn():
    name = request.form.get('name')
    codtype = request.form.get('codtype')
    sort = request.form.get('sort')
    type = request.form.get('type')
    id = request.form.get('id')
    print(id)
    if type == 1:
        level = 0
        db = Dd()
        orgn = db.get_all("select * from `organ` where level=0")
        orgn = len(orgn)
        coding = 'AO' + str(orgn + 1)
        print(coding)
        sql = "insert into  `organ` (name,coding,sort,level,codtype) values ('%s','%s',%s,%s,%s)" % (
            name, coding, sort, level, codtype)
        db.insert(sql)
        return {'code': '200', 'msg': '添加成功'}
    else:
        level = id
        db = Dd()
        data = db.get_one("select * from `organ` where id=%s" % id)
        orgn = db.get_all("select * from `organ` where level=%s" % id)
        orgn = len(orgn)
        print(data)
        coding = data['coding'] + 'AO' + str(orgn + 1)
        sql = "insert into  `organ` (name,coding,sort,level,codtype) values ('%s','%s',%s,%s,%s)" % (
            name, coding, sort, level, codtype)
        db.insert(sql)
        return {'code': '200', 'msg': '添加成功'}


# 获取详情
@views.route('/reliable_orgn', methods=['GET'])
def reliable_or():
    id = request.args.get('id')
    db = Dd()
    orgn = db.get_one("select * from `organ` where level=%s" % id)
    names = orgn['name']
    return {'code': '200', 'names': names}


from se import Myes
import json


@views.route('/esget')
def esget():
    es = Myes('testtabel', 'test-type')
    body = {
        # match 是单个
        #    "match":{
        #     "name":"abc"
        # }
        # multi_match是双个类型来筛选
        "multi_match": {
            "query": "abc",
            "fields": ["name", "content"]
        }
    }
    data = es.search(body)
    address_data = data['hits']['hits']
    print(address_data)
    return 'ok'


@views.route('/estestinsert')
def estest():
    es = Myes('testtabel', 'test-type')
    try:
        db = Dd()
        sql = "select * from test limit 0,10"
        data = db.get_all(sql)
        j = -1
        for i in data:
            j += 1
            es.insert(i)
        return 'true'
    except:
        print('error')
        return 'false'


@views.route('/get_userguan', methods=['GET'])
def get_userguan():
    page = request.args.get('page')
    if page:
        print(page)
        size = 10
        db = Dd()
        page = (int(page) - 1) * size
        sql = "select * from user limit %s,10" % page
        list = db.get_all(sql)
        return {'code': '200', 'list': list}
    else:
        rbac = RBAC()
        list = rbac.get_all_role()
        return {'code': '200', 'list': list}


@views.route('/post_user', methods=['POST'])
def post_user():
    username = request.form.get('username')
    password = request.form.get('password')
    passwords = request.form.get('passwords')
    phone = request.form.get('phone')
    email = request.form.get('email')
    jobnum = request.form.get('jobnum')
    geter = request.form.get('geter')
    name = request.form.get('name')
    print(username)
    if password != passwords:
        return {'code': '403', 'msg': '确认密码错误'}
    db = Dd()
    sql = "insert into  `user` (username,password,name,phone,email,jobnum,geter) values ('%s','%s','%s',%s,'%s',%s,%s)" % (
        username, password, name, phone, email, jobnum, geter)
    db.insert(sql)
    return {'code': '200', 'msg': '添加成功'}


@views.route('/delete_user', methods=['DELETE'])
def delete():
    pid = request.args.get('pid')
    print(pid)
    db = Dd()
    sql = "delete from `user` where id=%s" % pid
    db.delete(sql)
    return {'code': '200', 'msg': '删除成功'}


from tool import ACL, RBAC, r


# 获取权限
@views.route('/get_roleqan', methods=['GET'])
def get_roleqan():
    id = request.args.get('id')
    acl = ACL()
    list = acl.get_role(id)
    return {'code': '200', 'list': list}


@views.route('/login', methods=['POST'])
def login():
    username = request.form.get('username')
    password = request.form.get('password')
    sql = "select * from `user` where username='%s'" % (username)
    db = Dd()
    r = redis.Redis()
    data = db.get_one(sql)
    if data['user_type'] == 2:
        num = r.ttl('dongjie%s' % data['id'])
        return {'code': '403', 'msg': '你已冻结,还有%s秒解冻,请稍等' % num}
    if data['username'] != username and data['password'] != str(password):
        return {'code': 403, 'msg': '请重新登录'}
    myjwt = Myjwt()
    token = myjwt.encode_jwt({'username': data['username'], 'id': data['id']})
    return {'code': '200', 'userid': data['id'], 'token': token, 'msg': '登录成功', 'username': data['username']}


# 修改成冻结状态
@views.route('/update_one', methods=['PUT'])
def update_one():
    id = request.form.get('id')
    r = redis.Redis()
    db = Dd()
    db.update("update  `user` set user_type=2 where id=%s " % id)
    r.setex('dongjie%s' % id, 120, id)
    return {'code': 200, 'msg': '冻结成功'}


# 查看角色权限
@views.route('/get_node', methods=['GET'])
def get_node():
    id = request.args.get('id')
    db = Dd()
    list = db.get_all(
        "select node.name,node.id from `role` inner join `node_role` on role.id=node_role.roleid inner join node on node.id=node_role.node_id where role.id=%s" % id)
    data = []
    sql = 'select * from node where '
    sql1 = ''
    for j in list:
        sql1 += 'node.id != %s and ' % j['id']
    data_sql = sql + sql1

    data_sql = data_sql.strip('and ')
    # 获取有的数据
    try:
        list1 = db.get_all(data_sql)
        print(list1)
    except Exception as e:
        list1 = db.get_all("select * from node")
    for i in list1:
        if i['auid'] != 0:
            data.append({'value': i['id'], 'desc': i['name']})
    data1 = []
    for j in list:
        data1.append({'key': j['id'], 'label': j['name']})
    return {'code': '200', 'data': data, 'list': data1}


# 授权角色权限
@views.route('/post_liulan', methods=['POST'])
def post_liulan():
    pid = request.form.get('pid')
    print(pid)

    result = request.form.get('result')
    print(result)
    result = eval(result)
    rbac = RBAC()
    for i in result:
        rbac.set_role(pid, i)
    return 'ok'


# ABAC
def timer(func):
    @functools.wraps(func)
    def decor(*args, **kwargs):
        token = request.args.get('token')
        myjwt = Myjwt()
        num = myjwt.decode_jwt(token)
        print(num['id'])
        r = ABAC()
        data = r.get_all('abac')
        ip = request.remote_addr
        times = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
        db = Dd()
        for (key, value) in data.items():
            if key == 'ip':
                if str(value) != str(ip):
                    return 'ip限制'
            if key == 'time':
                starttime = eval(value)[0]
                end = eval(value)[1]
                if starttime > times and times > end:
                    return '时间限制'
            if key == 'role':
                roleid = db.get_one("select roleid from user where id=%s" % num['id'])
                if roleid['roleid'] != int(value):
                    return '角色限制'
            if request.method != 'GET':
                return '请求方法限制'
        return func(*args, **kwargs)

    return decor


from tool import ABAC
import time


@views.route('/hash_shu', methods=['GET'])
@timer
def hash_shu():
    r = ABAC()
    r.set_insert('abac', 'ip', '127.0.0.1')
    r.set_insert('abac', 'time', "['2020-10-15','2022-1-30']")
    r.set_insert('abac', 'role', '1')
    data = r.get_all('abac')
    times = datetime.datetime.strftime(datetime.datetime.now(), "%Y-%m-%d")
    for (key, value) in data.items():
        if key == 'time':
            print(eval(value)[0])
    return 'ok'


@views.route('/workflow', methods=['GET'])
def workflow1():
    sql = "select * from workflow"
    db = Dd()
    res = db.get_all(sql)
    return jsonify({'code': 200, 'list': res})


@views.route('/getresourcedata', methods=['POST', 'GET'])
def getresourcedata():
    if request.method == 'GET':
        # 从redis取，如果有直接返回
        values = r.str_get('dataresouce')
        if values:
            list = eval(values)
        else:
            # 如果没有查询
            sql = "select * from data_source"
            db1 = Dd()
            res = db1.get_all(sql)
            list = []
            if res:
                for i in res:
                    dict = {'id': i['id'], 'name': i['name']}
                    sql1 = "select id,name,type from %s" % (i['tablename'])
                    subres = db1.get_all(sql1)
                    dict['mes'] = subres
                    list.append(dict)
            # 存入redis
            r.str_set('dataresouce', json.dumps(list))
        return jsonify({"code": 200, 'list': list})
    else:
        data = request.data
        if data:
            data = json.loads(data)
        else:
            data = request.form
        sql = "update workflow set params='{}' where id = {}".format(data['mes'], int(data['id']))
        db1 = Dd()
        db1.update(sql)
        return jsonify({'code': 200})


@views.route('/getworkf', methods=['POST'])
def getworkf():
    data = request.data
    if data:
        data = json.loads(data)
    else:
        data = request.form
    sql = "select * from workflow where id={}".format(int(data['id']))
    db1 = Dd()
    res = db1.get_one(sql)
    print(res)
    return jsonify({'code': 200, 'workf': res})


def getdeptdata(data):
    tree = {}
    list = []
    for i in data:
        tree[i['id']] = i

    for j in data:
        pid = j['level']
        if not pid:
            j['type'] = 1
            list.append(j)
        else:
            if 'children' not in tree[pid]:
                tree[pid]['children'] = []
            # 查询此部门下所有员工
            sql = "select * from user where dept_id=%d" % (j['id'])
            db1 = Dd()
            res = db1.get_all(sql)
            userlist = []
            if res:
                for u in res:
                    u['type'] = 3
                    userlist.append(u)
            j['children'] = userlist
            j['type'] = 2
            tree[pid]['children'].append(j)

    return list


@views.route('/getcomanyuser')
def getcomanyuser():
    # 获取所有公司及所有部门
    sql = "select * from organ"
    db1 = Dd()
    res = db1.get_all(sql)
    mes = getdeptdata(res)
    return jsonify({'code': 200, 'list': mes})


@views.route('/mytaskaudit')
def mytaskaudit():
    id = request.args.get('id')
    # 从redis中获取我的任务列表
    key = 'task%d' % (int(id))
    print(key)
    task = r.get_all(key)
    list = []
    for k, v in task.items():
        id = str(k, encoding='utf8')
        title = str(v, encoding='utf8')
        list.append({'id': id, 'title': title})

    return jsonify({"code": 200, 'list': list})


def get_data():
    data = request.data
    if data:
        return json.loads(data)
    else:
        return request.form


@views.route('/tssk', methods=['POST'])
def task():
    if request.method == 'POST':
        data = ()
        id = data['id']
        print(id)
        sql = "select * from task where id = {}".format(int(id))
        db1 = Dd()
        task = db1.get_one(sql)
        sql1 = "select * from auto_increment where taskid={}".format(int(id))
        records = db1.findall(sql1)
        task['audit_records'] = records
        return jsonify({'code': 200, 'task': task})


@views.route('/taskrecordM', methods=['POST'])
def taskrecordM():
    data = get_data()
    # 写审批记录表
    time = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
    try:
        sql = "insert into auto_increment(taskid,userid,username,is_agree,reason,add_time) values(%d,%d,'%s',%d,'%s','%s')" % (
            int(data['taskid']), int(data['userid']), data['name'], int(data['agree']), data['reason'], time)
        db1 = Dd()
        db1.add(sql)
    except:
        return jsonify({"code": 10010, 'mes': '审批失败'})

    # 判断是否同意
    tsql = "select * from task where id=%d" % (int(data['taskid']))
    task = db1.find(tsql)
    key = 'task%d' % (int(data['userid']))
    # 清除当前redis中的信息
    r.del_one(key, int(data['taskid']))
    auditlist = json.loads(task['next_audit'])
    # 是不是最后一个审批人
    index = auditlist.index(data['userid'])
    if data['agree'] == '1':
        try:
            # 同意，查询任务，更新下一审批人，redis清除当前审批人，加入下一审批人，
            # 任务表中更新任务状态，如果是最后一个审批人，查新成已经完成，其它审核中
            if index == len(auditlist) - 1:
                sql1 = "update task set status=3,nexta=0 where id=%d" % (int(data['taskid']))
                db1.add(sql1)
            else:
                sql1 = "update task set status=2,nexta=%d where id=%d" % (auditlist[index + 1], int(data['taskid']))
                db1.add(sql1)
                # 添加redis
                newkey = 'task%d' % (int(auditlist[index + 1]))
                # 把下一审批人加入到redis
                r.set_insert(newkey, int(data['taskid']), task['title'])
            db1.commit()
        except:
            db1.rollback()
            return jsonify({"code": 10020, 'mes': '失败'})
    else:
        # 不同意，把redis中当前审批人清除，任务表中的下一审批人清空
        if index == 0:
            sql1 = "update task set status=1,nexta=0 where id=%d" % (int(data['taskid']))
            db1.update(sql1)
        else:
            sql1 = "update task set nexta=%d where id=%d" % (int(auditlist[index - 1]), int(data['taskid']))
            db1.update(sql1)
            # 添加redis
            newkey = 'task%d' % (int(auditlist[index - 1]))
            # 把下一审批人加入到redis
            r.set_insert(newkey, int(data['taskid']), task['title'])
    return jsonify({"code": 200})


@views.route('/send_message', methods=['GET', 'POST', 'PUT'])
def send_message():
    if request.method == 'POST':
        # 标题
        name = request.form.get('name')
        print(name)
        value1 = request.form.get('value1')
        value2 = request.form.get('value2')
        # 通告类型
        tongg = request.form.get('tongg')
        # 状态类型
        essage = request.form.get('essage')
        print(essage)
        centen = request.form.get('centen')
        print(centen)
        data = request.form
        print(data)
        list = []
        for (i, tiem) in data.items():
            if i[:-3] == 'result':
                list.append(tiem)
        # result=request.form.get('result')
        # print(result)
        db = Dd()
        # print("insert into massage (title,canten,state,category) values ('%s','%s',%s,%s)" %(name,centen,essage,tongg))
        db.insert("insert into message (title,canten,state,category) values ('%s','%s',%s,%s)" % (
            name, centen, essage, tongg))
        # print("select * from message where title='%s'" % name)
        res = db.get_one("select * from message where title='%s'" % name)
        # 添加用户消息
        if int(tongg) == 2:
            print(25)
            for i in list:
                db.insert("insert into user_message (messageid,userid,state,add_time) values (%s,%s,%s,'%s')" % (
                    res['id'], i, 2, datetime.datetime.now()))
        return {'code': '200'}


@views.route('/noticem', methods=['POST'])
def noticem():
    data1 = get_data()
    print(data1)
    now = datetime.datetime.strftime(datetime.datetime.now(), '%Y-%m-%d %H:%M:%S')
    sql = "insert into news(title, news_type, level, type, content, ctime) values('{}', {}, {}, {}, '{}', '{}')".format(
        data1['title'], int(data1['news_type']), int(data1['level']), int(data1['type']), data1['content'], str(now))
    db1 = Dd()
    id = db1.update(sql)
    if int(data1['type']) == 3:
        list1 = data1['userids']
        for i in list1:
            aql1 = "insert into user_news(newsid, userid, ctime) values ({}, {}, '{}') ".format(int(id), int(i), now)
            db1.update(aql1)
    return jsonify({'code': 200})


@views.route('/getnotice')
def getnotice():
    userid = request.args.get("id")
    sql = "select news.*, user_news.state from news left join user_news on news.id = user_news.newsid where news.type = 1 or news.id in(select newsid from user_news where userid={}) order by news.ctime, news.level".format(
        int(userid))
    db1 = Dd()
    res = db1.get_all(sql)
    mes = []
    for i in res:
        if i['type'] == 1:
            key = 'notice' + str(i['id'])
            list1 = r.set_insert(key)
            if userid in list1:
                i['state'] = 1
        mes.append(i)
    return jsonify({'code': 200, 'list': mes})

@views.route('/customer_reg', methods=['POST','GET'])
def customer_reg():
    import time
    if request.method == 'POST':
        data = get_data()['data']
        print(data)
        now = int(time.time())
        sql = "insert into customer(name,company_name,managerid,reformid,addtime) values('%s','%s',%d,%d,%d)" % (
            data['name'], data['company'], int(data['manager_id']), int(data['refromid']), now)
        db1.update(sql)
    else:
        print('11111111111111111')
    return jsonify({"code": 200})


@views.route('/managelist')
def managelist():
    sql = "select * from customer_manager"
    res = db1.get_all(sql)
    return jsonify({"code": 200, 'list': res})


@views.route('/resourcelist')
def resourcelist():
    sql = "select * from reg_form"
    print(sql)
    res = db1.get_all(sql)
    return jsonify({"code": 200, 'list': res})

